/**
 * @name         RepNET Common methods 
 * @version      1.0a
 * @author       Kasun Rathnayaka <kazar55@gmail.com>
 * @author       Ramindu Deshapriya <rasade88@gmail.com>
 * @author 		 Thusira Dissanayake <thusira14@gmail.com>
 * @about        Developed by MiDi
 * @license	 http://www.gnu.org/licenses/lgpl-2.1.html GNU Lesser General Public License (LGPL)
 * @lastModified 2012.09.24
 */
package com.midi.wholesale.main;

import java.awt.event.ItemListener;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import com.midi.wholesale.main.Main;
import com.midi.wholesale.models.*;
import com.midi.wholesale.ui.*;
import com.mysql.jdbc.PreparedStatement;

public class Common {
	static ResultSet itemListResult;
	public static ArrayList<Lorry> lorries = null;

	/**
	 * Method to return list of lorry names to populate combo box
	 * 
	 * @return <strong>ArrayList</strong> List of lorry names from the database
	 */
	public static ArrayList<Lorry> loadlorries() {
		ArrayList<Lorry> lorries = new ArrayList<Lorry>();
		try {
			Statement statement = Main.dbConnection.createStatement();
			ResultSet res = statement.executeQuery("SELECT * FROM lorries");
			while (res.next()) {
				lorries.add(new Lorry(res.getInt("id"), res.getString("name")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return lorries;
	}

	public static ArrayList<Agent> agents = null;

	/**
	 * Method to return list of agent names to populate combo box
	 * 
	 * @return <strong>ArrayList</strong> List of agent names from the database
	 */
	public static ArrayList<Agent> loadagents() {
		ArrayList<Agent> agents = new ArrayList<Agent>();
		try {
			Statement statement = Main.dbConnection.createStatement();
			ResultSet res = statement.executeQuery("SELECT * FROM agents");
			while (res.next()) {
				agents.add(new Agent(res.getInt("id"), res.getString("name")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return agents;
	}

	public static ArrayList<Route> routes = null;

	/**
	 * Method to return list of route names to populate combo box
	 * 
	 * @return <strong>ArrayList</strong> List of route names from the database
	 */
	public static ArrayList<Route> loadroutes() {
		ArrayList<Route> routes = new ArrayList<Route>();
		try {
			Statement statement = Main.dbConnection.createStatement();
			ResultSet res = statement.executeQuery("SELECT * FROM routs");
			while (res.next()) {
				routes.add(new Route(res.getInt("id"), res.getString("name")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return routes;
	}

	/**
	 * Method to return list of item names to populate combo box
	 * 
	 * @return <strong>ArrayList</strong> List of item names from the database
	 */
	public static ArrayList<Item> loadItems() {
		ArrayList<Item> items = new ArrayList<Item>();
		try {
			Statement statement = Main.dbConnection.createStatement();
			ResultSet res = statement.executeQuery("SELECT * FROM items");
			while (res.next()) {
				items.add(new Item(res.getInt("id"), res.getString("name")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return items;
	}
//SELECT items.name,sum(deliveryinfo.amount-deliveryinfo.remainder) as Sales from items inner join deliveryinfo ON items.id=deliveryinfo.item_id group by deliveryinfo.item_id

	/**
	 * Method to return an ArrayList of delivery item results
	 * 
	 * @param selectedItem
	 * @param selectedItem2
	 * @param selectedItem3
	 * @return
	 */
	public static ArrayList<HashMap<String, String>> loadDelivery(Lorry lorry) {
		ArrayList<HashMap<String, String>> results = new ArrayList<HashMap<String, String>>();
		try {
			PreparedStatement statement1 = (PreparedStatement) Main.dbConnection
					.prepareStatement("SELECT i.name, d.amount FROM delivery as d LEFT JOIN items as i ON i.id=d.item_id WHERE d.lorry_id=?");
			statement1.setString(1, String.valueOf(lorry.getId()));
			// statement1.setString(2, String.valueOf(route.getId()));
			itemListResult = statement1.executeQuery();
			while (itemListResult.next()) {
				HashMap<String, String> line = new HashMap<String, String>();
				line.put("item_name", itemListResult.getString("name"));
				line.put("amount", itemListResult.getString("amount"));
				results.add(line);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return results;
	}

	/**
	 * Method to return an ArrayList of delivery item results for day end
	 * 
	 * @param selectedItem
	 * @param selectedItem2
	 * @param selectedItem3
	 * @return
	 */
	public static ArrayList<HashMap<String, String>> dayEndloadDelivery(
			Lorry lorry) {
		ArrayList<HashMap<String, String>> results = new ArrayList<HashMap<String, String>>();
		try {
			PreparedStatement statement1 = (PreparedStatement) Main.dbConnection
					.prepareStatement("SELECT i.name, d.amount FROM delivery as d LEFT JOIN items as i ON i.id=d.item_id WHERE d.lorry_id=?");
			statement1.setString(1, String.valueOf(lorry.getId()));
			// statement1.setString(2, String.valueOf(route.getId()));
			itemListResult = statement1.executeQuery();
			while (itemListResult.next()) {
				HashMap<String, String> line = new HashMap<String, String>();
				line.put("item_name", itemListResult.getString("name"));
				line.put("amount", itemListResult.getString("amount"));
				results.add(line);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return results;
	}

	public static void editDelivery(String lorryName, String itemName,
			String amount) {

		try {
			;
			ResultSet res1 = Main.dbConnection.createStatement().executeQuery(
					"select id from lorries where name='" + lorryName + "'");
			res1.next();
			String lorryId = res1.getString("id");

			res1 = Main.dbConnection.createStatement().executeQuery(
					"select id from items where name='" + itemName + "'");
			res1.next();
			String itemId = res1.getString("id");

			Main.dbConnection.createStatement().executeUpdate(
					"INSERT INTO delivery (lorry_id, item_id, amount) VALUES ('"
							+ lorryId + "', '" + itemId + "','" + amount
							+ "') ON DUPLICATE KEY UPDATE lorry_id='" + lorryId
							+ "', item_id='" + itemId + "', amount='" + amount
							+ "'");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	// public static void editDelivery(String lorryName,String itemName, String
	// amount){}

}
